/*
 * Hibernate, Relational Persistence for Idiomatic Java
 *
 * Copyright (c) 2010, Red Hat Inc. or third-party contributors as
 * indicated by the @author tags or express copyright attribution
 * statements applied by the authors.  All third-party contributions are
 * distributed under license by Red Hat Inc.
 *
 * This copyrighted material is made available to anyone wishing to use, modify,
 * copy, or redistribute it subject to the terms and conditions of the GNU
 * Lesser General Public License, as published by the Free Software Foundation.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
 * or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License
 * for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with this distribution; if not, write to:
 * Free Software Foundation, Inc.
 * 51 Franklin Street, Fifth Floor
 * Boston, MA  02110-1301  USA
 */
package net.hands.ado.db.jdbc.dialect;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.io.StringReader;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Calendar;
import java.util.HashSet;
import java.util.Properties;
import java.util.Set;

import net.hands.ado.db.DBException;
import net.hands.ado.db.TypeNames;
import net.hands.common.ClassUtils;
import net.hands.common.Convert;
import net.hands.common.FileUtils;
import net.hands.common.IoUtils;
import net.hands.common.ObjectUtils;
import net.hands.common.StringUtils;

/**
 * Represents a dialect of SQL implemented by a particular RDBMS. Subclasses
 * implement Hibernate compatibility with different systems.<br>
 * <br>
 * Subclasses should provide a public default constructor that
 * <tt>register()</tt> a set of Type mappings and default Hibernate properties.<br>
 * <br>
 * Subclasses should be immutable.
 * 
 * @author Gavin King, David Channon
 */
public abstract class Dialect {

	public static final String DEFAULT_BATCH_SIZE = "15";
	public static final String NO_BATCH = "0";

	/**
	 * Characters used for quoting SQL identifiers
	 */
	public static final String QUOTE = "`\"[";
	public static final String CLOSED_QUOTE = "`\"]";

	private final TypeNames typeNames = new TypeNames();

	// private final Properties properties = new Properties();
	// private final Map<String, SQLFunction> sqlFunctions = new HashMap<String,
	// SQLFunction>();
	private final Set<String> sqlKeywords = new HashSet<String>();

	// private final UniqueDelegate uniqueDelegate;

	// constructors and factory methods ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

	protected Dialect() {
		registerColumnType(Types.BIT, "bit");
		registerColumnType(Types.BOOLEAN, "boolean");
		registerColumnType(Types.TINYINT, "tinyint");
		registerColumnType(Types.SMALLINT, "smallint");
		registerColumnType(Types.INTEGER, "integer");
		registerColumnType(Types.BIGINT, "bigint");
		registerColumnType(Types.FLOAT, "float($p)");
		registerColumnType(Types.DOUBLE, "double precision");
		registerColumnType(Types.NUMERIC, "numeric($p,$s)");
		registerColumnType(Types.REAL, "real");
		registerColumnType(Types.DATE, "date");
		registerColumnType(Types.TIME, "time");
		registerColumnType(Types.TIMESTAMP, "timestamp");
		registerColumnType(Types.VARBINARY, "bit varying($l)");
		registerColumnType(Types.LONGVARBINARY, "bit varying($l)");
		registerColumnType(Types.BLOB, "blob");
		registerColumnType(Types.CHAR, "char($l)");
		registerColumnType(Types.VARCHAR, "varchar($l)");
		registerColumnType(Types.LONGVARCHAR, "varchar($l)");
		registerColumnType(Types.CLOB, "clob");
		registerColumnType(Types.NCHAR, "nchar($l)");
		registerColumnType(Types.NVARCHAR, "nvarchar($l)");
		registerColumnType(Types.LONGNVARCHAR, "nvarchar($l)");
		registerColumnType(Types.NCLOB, "nclob");
	}

	@Override
	public String toString() {
		return getClass().getName();
	}

	/**
	 * Subclasses register a Type name for the given Type code. <tt>$l</tt> in
	 * the Type name with be replaced by the column length (if appropriate).
	 * 
	 * @param code
	 *            The {@link java.sql.Types} typecode
	 * @param name
	 *            The database Type name
	 */
	protected void registerColumnType(int code, String name) {
		typeNames.put(code, name);
	}

	/**
	 * Subclasses register a Type name for the given Type code and maximum
	 * column length. <tt>$l</tt> in the Type name with be replaced by the
	 * column length (if appropriate).
	 * 
	 * @param code
	 *            The {@link java.sql.Types} typecode
	 * @param capacity
	 *            The maximum length of database Type
	 * @param name
	 *            The database Type name
	 */
	protected void registerColumnType(int code, long capacity, String name) {
		typeNames.put(code, capacity, name);
	}

	/**
	 * Get the name of the database Type associated with the given
	 * {@link java.sql.Types} typecode with the given storage specification
	 * parameters.
	 * 
	 * @param code
	 *            The {@link java.sql.Types} typecode
	 * @param length
	 *            The datatype length
	 * @param precision
	 *            The datatype precision
	 * @param scale
	 *            The datatype scale
	 * @return the database Type name
	 * @throws HibernateException
	 *             If no mapping was specified for that Type.
	 */
	public String getTypeName(int code, long length, int precision, int scale)
			throws DBException {
		String result = typeNames.get(code, length, precision, scale);
		if (result == null) {
			throw DBException.of(String.format(
					"No Type  for java.sql.Types code: %s, length: %s", code,
					length));
		}
		return result;
	}

	public String getTypeName(int code) {
		String result = typeNames.get(code);
		if (result == null) {
			throw DBException.of("No Type  for java.sql.Types code");
		}
		return result;
	}

	// keyword support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

	protected void registerKeyword(String word) {
		sqlKeywords.add(word);
	}

	public Set<String> getKeywords() {
		return sqlKeywords;
	}

	/**
	 * Get the string to append to SELECT statements to acquire locks for this
	 * dialect. 更新表数据
	 * 
	 * @return The appropriate <tt>FOR UPDATE</tt> clause string.
	 */
	public String getForUpdateString() {
		return " for update";
	}

	/**
	 * Command used to create a table. 创建表
	 * 
	 * @return The command used to create a table.
	 */
	public String getCreateTableString() {
		return "create table";
	}

	/**
	 * drop语句 支持if exists 在表名 前
	 * 
	 * @return
	 */
	public boolean supportsIfExistsBeforeTableName() {
		return false;
	}

	/**
	 * drop语句 支持if exists 在表名 后
	 * 
	 * @return
	 */
	public boolean supportsIfExistsAfterTableName() {
		return false;
	}

	/**
	 * 删除表
	 * 
	 * @param tableName
	 * @return
	 */
	public String getDropTableString(String tableName) {
		StringBuilder buf = new StringBuilder("drop table ");
		if (supportsIfExistsBeforeTableName()) {
			buf.append("if exists ");
		}
		buf.append(tableName);

		if (supportsIfExistsAfterTableName()) {
			buf.append(" if exists");
		}
		return buf.toString();
	}

	/**
	 * The name of the database-specific SQL function for retrieving the current
	 * timestamp.
	 * 
	 * @return The function name.
	 */
	public String getCurrentTimestampSQLFunctionName() {
		// the standard SQL function name is current_timestamp...
		return "current_timestamp";
	}

	/**
	 * The fragment used to insert a row without specifying any column values.
	 * This is not possible on some databases.
	 * 
	 * @return The appropriate empty values clause.
	 */
	public String getNoColumnsInsertString() {
		return "values ( )";
	}

	/**
	 * The name of the SQL function that transforms a string to lowercase
	 * 
	 * @return The dialect-specific lowercase function.
	 */
	public String getLowercaseFunction() {
		return "lower";
	}

	/**
	 * The name of the SQL function that can do case insensitive <b>like</b>
	 * comparison.
	 * 
	 * @return The dialect-specific "case insensitive" like function.
	 */
	public String getCaseInsensitiveLike() {
		return "like";
	}

	/**
	 * What is the maximum length Hibernate can use for generated aliases?
	 * 
	 * @return The maximum length.
	 */
	public int getMaxAliasLength() {
		return 10;
	}

	/**
	 * The SQL literal value to which this database maps boolean values.
	 * 
	 * @param bool
	 *            The boolean value
	 * @return The appropriate SQL literal.
	 */
	public String toBooleanValueString(boolean bool) {
		return bool ? "1" : "0";
	}

	// identifier quoting support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

	/**
	 * The character specific to this dialect used to begin a quoted identifier.
	 * 
	 * @return The dialect's specific open quote character.
	 */
	public char openQuote() {
		return '"';
	}

	/**
	 * The character specific to this dialect used to close a quoted identifier.
	 * 
	 * @return The dialect's specific close quote character.
	 */
	public char closeQuote() {
		return '"';
	}

	/**
	 * Apply dialect-specific quoting.
	 * <p/>
	 * By default, the incoming value is checked to see if its first character
	 * is the back-tick (`). If so, the dialect specific quoting is applied.
	 * 
	 * @param name
	 *            The value to be quoted.
	 * @return The quoted (or unmodified, if not starting with back-tick) value.
	 * @see #openQuote()
	 * @see #closeQuote()
	 */
	public final String quote(String name) {
		if (name == null) {
			return null;
		}

		if (name.charAt(0) == '`') {
			return openQuote() + name.substring(1, name.length() - 1)
					+ closeQuote();
		} else {
			return name;
		}
	}

	/**
	 * The syntax used to add a column to a table (optional). 添加表字段
	 * 
	 * @return The "add column" fragment.
	 */
	public String getAddColumnString(String tableName, String columnName) {
		throw new UnsupportedOperationException(
				"No add column syntax supported by " + getClass().getName());
	}

	/**
	 * 删除字段
	 * 
	 * @param tableName
	 * @param columnName
	 * @return
	 */
	public String getDropColumnString(String tableName, String columnName) {
		throw new UnsupportedOperationException(
				"No add column syntax supported by " + getClass().getName());
	}

	public String getDropForeignKeyString() {
		return " drop constraint ";
	}

	/**
	 * The syntax used to add a primary key constraint to a table.
	 * 
	 * @param constraintName
	 *            The name of the PK constraint.
	 * @return The "add PK" fragment
	 */
	public String getAddPrimaryKeyConstraintString(String constraintName) {
		return " add constraint " + constraintName + " primary key ";
	}

	/**
	 * The keyword used to specify a nullable column.
	 * 
	 * @return String
	 */
	public String getNullColumnString() {
		return "";
	}

	/**
	 * 表注释
	 * 
	 * @param comment
	 * @return
	 */
	public String getTableComment(String comment) {
		return "";
	}

	/**
	 * 表列注释
	 * 
	 * @param comment
	 * @return
	 */
	public String getColumnComment(String comment) {
		return " comment '" + comment + "'";
	}

	/**
	 * 是否支持注释
	 * 
	 * @return
	 */
	public boolean supportsCommentOn() {
		return false;
	}

	/**
	 * @return Returns the separator to use for defining cross joins when
	 *         translating HQL queries.
	 *         <p/>
	 *         Typically this will be either [<tt> cross join </tt>] or [
	 *         <tt>, </tt>]
	 *         <p/>
	 *         Note that the spaces are important!
	 * 
	 */
	public String getCrossJoinSeparator() {
		return " cross join ";
	}

	/**
	 * Return the limit that the underlying database places on the number
	 * elements in an {@code IN} predicate. If the database defines no such
	 * limits, simply return zero or less-than-zero.
	 * 
	 * @return int The limit, or zero-or-less to indicate no limit.
	 */
	public int getInExpressionCountLimit() {
		return 0;
	}

	/**
	 * 分页
	 * 
	 * @param sql
	 * @param i
	 * @param fetchSize
	 * @return
	 */
	public String toLimitSQL(final String sql, final int i, final int fetchSize) {
		return sql;
	}

	public String getNotExpression(String expression) {
		return "not " + expression;
	}

	/*****
	 * 
	 * 添加约束
	 * 
	 * 
	 * 
	 */

	public String addUniquesOnAlter(String tableName, String uk,
			String[] uniqueArr) {
		// Do this here, rather than allowing UniqueKey/Constraint to do it.
		// We need full, simplified control over whether or not it happens.
		return new StringBuilder("alter table ").append(tableName)
				.append(" add constraint ").append(uk)
				.append(uniqueConstraintSql(uniqueArr)).toString();
	}

	/**
	 * 删除约束
	 * 
	 * @param uniqueKey
	 * @return
	 */
	public String dropUniquesOnAlter(String tableName, String uk) {
		// Do this here, rather than allowing UniqueKey/Constraint to do it.
		// We need full, simplified control over whether or not it happens.

		return new StringBuilder("alter table ").append(tableName)
				.append(" drop constraint ").append(uk).toString();
	}

	/**
	 * 唯一约束sql
	 * 
	 * @param uniqueArr
	 * @return
	 */
	public String uniqueConstraintSql(String[] uniqueArr) {
		StringBuilder sb = new StringBuilder();
		sb.append(" unique (");
		for (int i = 0; i < uniqueArr.length; i++) {
			sb.append(uniqueArr[i]);
			if (i < uniqueArr.length - 1) {
				sb.append(", ");
			}
		}

		return sb.append(')').toString();
	}

	/**
	 * 创建索引
	 * 
	 * @param tableName
	 * @param unique
	 * @param indexkey
	 * @param indexArr
	 * @return
	 */
	public String createIndex(String tableName, Boolean unique,
			String indexkey, String[] indexArr) {
		StringBuilder buf = new StringBuilder("create")
				.append(unique ? " unique" : "").append(" index ")
				.append(indexkey).append(" on ").append(tableName).append(" (");
		for (int i = 0; i < indexArr.length; i++) {
			buf.append(indexArr[i]);
			if (i < indexArr.length - 1)
				buf.append(", ");
		}
		buf.append(")");
		return buf.toString();
	}

	public String dropIndex(String tableName, String indexkey) {
		return "drop index " + StringUtils.qualify(tableName, indexkey);
	}

	public abstract SQLFunction getFunction();

	/**
	 * 
	 * @param rs
	 * @param columnIndex
	 * @return
	 */
	public Object getResultSetValue(final ResultSet rs, final int columnIndex)
			throws SQLException {
		Object obj = null;

		if (obj == null) {
			obj = rs.getObject(columnIndex);
		}

		if (obj instanceof Blob) {
			obj = ((Blob) obj).getBinaryStream();
		} else if (obj instanceof Clob) {
			obj = ((Clob) obj).getCharacterStream();
		} else if (obj instanceof java.sql.Date) {
			if ("java.sql.Timestamp".equals(rs.getMetaData()
					.getColumnClassName(columnIndex))) {
				obj = rs.getTimestamp(columnIndex);
			}
		}

		if (obj != null && rs.wasNull()) {
			obj = null;
		}
		return obj;

	}

	protected int getParameterType(final Class<?> paramType) {
		if (paramType == null) {
			return Types.NULL;
		}
		if (String.class.equals(paramType)) {
			return Types.VARCHAR;
		} else if (Enum.class.isAssignableFrom(paramType)) {
			// 枚举保存索引
			return Types.NUMERIC;
		} else if (boolean.class.equals(paramType)
				|| Boolean.class.equals(paramType)) {
			// 数据库一般没有boolean类型，这里用NUMERIC替代
			return Types.NUMERIC;
		} else if (int.class.equals(paramType) || long.class.equals(paramType)
				|| double.class.equals(paramType)
				|| float.class.equals(paramType)
				|| short.class.equals(paramType)
				|| byte.class.equals(paramType)
				|| Number.class.isAssignableFrom(paramType)) {
			return Types.NUMERIC;
		} else if (java.util.Date.class.equals(paramType)
				|| java.util.Calendar.class.equals(paramType)) {
			return Types.TIMESTAMP;
		} else if (java.sql.Timestamp.class.equals(paramType)) {
			return Types.TIMESTAMP;
		} else if (java.sql.Date.class.equals(paramType)) {
			return Types.DATE;
		} else if (java.sql.Time.class.equals(paramType)) {
			return Types.TIME;
		} else if (byte[].class.equals(paramType)
				|| InputStream.class.isAssignableFrom(paramType)) {
			return Types.BLOB;
		} else if (char[].class.equals(paramType)
				|| Properties.class.equals(paramType)
				|| Reader.class.isAssignableFrom(paramType)) {
			return Types.CLOB;
		}
		return Types.NULL;
	}

	public void setParameterValue(final PreparedStatement ps,
			final int paramIndex, final Object inValue) throws SQLException,
			IOException {
		final int sqlType = getParameterType(inValue == null ? null : inValue
				.getClass());
		boolean object = false;
		if (inValue == null) {
			ps.setNull(paramIndex, sqlType);
		} else {
			if (sqlType == Types.VARCHAR || sqlType == Types.LONGVARCHAR) {
				ps.setString(paramIndex, inValue.toString());
			} else if (sqlType == Types.INTEGER || sqlType == Types.TINYINT
					|| sqlType == Types.SMALLINT || sqlType == Types.BIT
					|| sqlType == Types.BIGINT || sqlType == Types.FLOAT
					|| sqlType == Types.DOUBLE || sqlType == Types.NUMERIC
					|| sqlType == Types.DECIMAL) {
				if (inValue instanceof Long) {
					ps.setLong(paramIndex, (Long) inValue);
				} else if (inValue instanceof Integer) {
					ps.setInt(paramIndex, (Integer) inValue);
				} else if (inValue instanceof Short) {
					ps.setShort(paramIndex, (Short) inValue);
				} else if (inValue instanceof Byte) {
					ps.setByte(paramIndex, (Byte) inValue);
				} else if (inValue instanceof Float) {
					ps.setFloat(paramIndex, (Float) inValue);
				} else if (inValue instanceof Double) {
					ps.setDouble(paramIndex, (Double) inValue);
				} else if (inValue instanceof BigDecimal) {
					ps.setBigDecimal(paramIndex, (BigDecimal) inValue);
				} else {
					object = true;
				}
			} else if (sqlType == Types.BOOLEAN) {
				ps.setBoolean(paramIndex, Convert.toBool(inValue));
			} else if (sqlType == Types.DATE) {
				if (inValue instanceof java.util.Date) {
					if (inValue instanceof java.sql.Date) {
						ps.setDate(paramIndex, (java.sql.Date) inValue);
					} else {
						ps.setDate(paramIndex, new java.sql.Date(
								((java.util.Date) inValue).getTime()));
					}
				} else if (inValue instanceof Calendar) {
					final Calendar cal = (Calendar) inValue;
					ps.setDate(paramIndex, new java.sql.Date(cal.getTime()
							.getTime()), cal);
				} else {
					object = true;
				}
			} else if (sqlType == Types.TIME) {
				if (inValue instanceof java.util.Date) {
					if (inValue instanceof java.sql.Time) {
						ps.setTime(paramIndex, (java.sql.Time) inValue);
					} else {
						ps.setTime(paramIndex, new java.sql.Time(
								((java.util.Date) inValue).getTime()));
					}
				} else if (inValue instanceof Calendar) {
					final Calendar cal = (Calendar) inValue;
					ps.setTime(paramIndex, new java.sql.Time(cal.getTime()
							.getTime()), cal);
				} else {
					object = true;
				}
			} else if (sqlType == Types.TIMESTAMP) {
				if (inValue instanceof java.util.Date) {
					if (inValue instanceof java.sql.Timestamp) {
						ps.setTimestamp(paramIndex,
								(java.sql.Timestamp) inValue);
					} else {
						ps.setTimestamp(paramIndex, new java.sql.Timestamp(
								((java.util.Date) inValue).getTime()));
					}
				} else if (inValue instanceof Calendar) {
					final Calendar cal = (Calendar) inValue;
					ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal
							.getTime().getTime()), cal);
				} else {
					object = true;
				}
			} else if (sqlType == Types.BLOB || sqlType == Types.BINARY
					|| sqlType == Types.VARBINARY
					|| sqlType == Types.LONGVARBINARY) {
				if (inValue instanceof byte[]) {
					ps.setBytes(paramIndex, (byte[]) inValue);
				} else if (inValue instanceof InputStream) {
					initPreparedStatementMethod(ps.getClass());
					if (setBinaryStreamMethod != null) {
						ClassUtils.invoke(setBinaryStreamMethod, ps,
								paramIndex, inValue);
					} else if (inValue instanceof ByteArrayInputStream) {
						try {
							ps.setBinaryStream(paramIndex,
									(InputStream) inValue, Convert.toInt(
											ClassUtils.getFieldValue("count",
													inValue), -1));
						} catch (final NoSuchFieldException e) {
						}
					} else if (inValue instanceof FileInputStream) {
						final FileInputStream fStream = (FileInputStream) inValue;
						ps.setBinaryStream(paramIndex, fStream, (int) fStream
								.getChannel().size());
					} else {
						final File file = new File(
								System.getProperty("java.io.tmpdir")
										+ ObjectUtils.hashStr(inValue));
						FileUtils.copyFile((InputStream) inValue, file);
						final FileInputStream fStream = new FileInputStream(
								file);
						ps.setBinaryStream(paramIndex, fStream, (int) fStream
								.getChannel().size());
					}
				} else {
					object = true;
				}
			} else if (sqlType == Types.CLOB) {
				if (inValue instanceof char[]) {
					ps.setString(paramIndex, new String((char[]) inValue));
				} else if (inValue instanceof Properties) {
					final Properties props = (Properties) inValue;
					if (props.size() > 0) {
						ps.setString(paramIndex, Convert.toString(props, null));
					} else {
						ps.setNull(paramIndex, sqlType);
					}
				} else if (inValue instanceof Reader) {
					initPreparedStatementMethod(ps.getClass());
					if (setCharacterStreamMethod != null) {
						ClassUtils.invoke(setCharacterStreamMethod, ps,
								paramIndex, inValue);
					} else if (inValue instanceof StringReader) {
						try {
							ps.setString(paramIndex, (String) ClassUtils
									.getFieldValue("str", inValue));
						} catch (final NoSuchFieldException e) {
						}
					} else {
						ps.setString(paramIndex,
								IoUtils.getStringFromReader((Reader) inValue));
					}
				} else {
					object = true;
				}
			}
			if (object == true) {
				ps.setObject(paramIndex, inValue, sqlType);
			}
		}
	}

	private Method setBinaryStreamMethod, setCharacterStreamMethod;
	private boolean _bPreparedStatementMethod = false;

	private void initPreparedStatementMethod(
			final Class<? extends PreparedStatement> pClass) {
		if (_bPreparedStatementMethod) {
			return;
		}
		try {
			// jdbc4
			setBinaryStreamMethod = pClass.getMethod("setBinaryStream",
					int.class, InputStream.class);
			setCharacterStreamMethod = pClass.getMethod("setCharacterStream",
					int.class, Reader.class);
			_bPreparedStatementMethod = true;
		} catch (final NoSuchMethodException e) {
		}
	}

	public String toCountSQL(final String sql) {
		final StringBuilder sb = new StringBuilder();

		if (sb.length() == 0) {
			sb.append("select" + getFunction().count() + " from (").append(sql)
					.append(") t_count");
		}
		return sb.toString();
	}

	public int getResultSetType() {
		return ResultSet.TYPE_FORWARD_ONLY;
	}

}
